﻿
CREATE TABLE ProductGroup(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(max) NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_ProductGroup PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE Product(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(max) NULL,
	Price float NOT NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	ProductGroupName nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_Product PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE PrincipalGame(
	ID int IDENTITY(1,1) NOT NULL,
	PrincipalA nvarchar(max) NULL,
	PrincipalB nvarchar(max) NULL,
 CONSTRAINT PK_PrincipalGame PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE Department(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(max) NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	CompanyName nvarchar(max) NULL,
	CenterName nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_Department PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE DateTargets(
	ID int IDENTITY(1,1) NOT NULL,
	CompanyName nvarchar(max) NULL,
	CenterName nvarchar(max) NULL,
	DepartmentName nvarchar(max) NULL,
	Amount float NOT NULL,
	BeginDate datetime NOT NULL,
	EndDate datetime NOT NULL,
 CONSTRAINT PK_DateTargets PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE Customer(
	OID nvarchar(128) NOT NULL,
	Name nvarchar(max) NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	CompanyOID nvarchar(max) NULL,
	CenterOID nvarchar(max) NULL,
	DepartmentOID nvarchar(max) NULL,
	StaffOID nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED 
(
	OID ASC
)
) 

GO

CREATE TABLE Company(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(max) NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_Company PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE Center(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(max) NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	CompanyName nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_Center PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE Billing(
	TransType nvarchar(128) NOT NULL,
	ID int IDENTITY(1,1) NOT NULL,
	Operator nvarchar(max) NULL,
	LastOprator nvarchar(max) NULL,
	CompanyName nvarchar(max) NULL,
	CenterName nvarchar(max) NULL,
	DepartmentName nvarchar(max) NULL,
	StaffName nvarchar(max) NULL,
	CustomerName nvarchar(max) NULL,
	ProductName nvarchar(max) NULL,
	ProductGroupName nvarchar(max) NULL,
	SalesTypeName nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	CRM_OrderNo nvarchar(max) NULL,
	IsNewCustomer bit NOT NULL,
	Amount float NOT NULL,
	TransDate datetime NOT NULL,
	LastModifyTime datetime NOT NULL,
	CreateTime datetime NOT NULL,
	IsConfirm bit NOT NULL,
	IsAudit tinyint NOT NULL,
 CONSTRAINT PK_Billing PRIMARY KEY CLUSTERED 
(
	TransType ASC,
	ID ASC
)
) 

GO



GO

CREATE TABLE Staff(
	ID int IDENTITY(1,1) NOT NULL,
	Name nvarchar(max) NULL,
	Sex bit NOT NULL,
	EntryDate datetime NOT NULL,
	ExitDate datetime NOT NULL,
	Principal nvarchar(max) NULL,
	Remark nvarchar(max) NULL,
	CompanyName nvarchar(max) NULL,
	CenterName nvarchar(max) NULL,
	DepartmentName nvarchar(max) NULL,
	StaffLevel nvarchar(max) NULL,
	TargetAmount nvarchar(max) NULL,
	IsNormal bit NOT NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_Staff PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE SaleTarget(
	ID int IDENTITY(1,1) NOT NULL,
	CompanyName nvarchar(max) NULL,
	CenterName nvarchar(max) NULL,
	DepartmentName nvarchar(max) NULL,
	Amount float NOT NULL,
	Month datetime NOT NULL,
 CONSTRAINT PK_SaleTarget PRIMARY KEY CLUSTERED 
(
	ID ASC
)
) 

GO

CREATE TABLE SysUser(
	UserName nvarchar(128) NOT NULL,
	UserPwd nvarchar(256) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_SysUser PRIMARY KEY CLUSTERED 
(
	UserName ASC
)
) 

GO

CREATE TABLE SysRole(
	RoleName nvarchar(128) NOT NULL,
	RoleDescription nvarchar(max) NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_SysRoles PRIMARY KEY CLUSTERED 
(
	RoleName ASC
)
) 

CREATE TABLE SysUserRole(
	UserName nvarchar(128) NOT NULL,
	RoleName nvarchar(128) NOT NULL,
 CONSTRAINT PK_SysUserRole PRIMARY KEY CLUSTERED 
(
	UserName ASC,
	RoleName ASC
)
) 

GO

CREATE TABLE RoleAccess(
	OID nvarchar(128) NOT NULL,
	FormName nvarchar(max) NULL,
	ControlName nvarchar(max) NULL,
	Enabled bit NOT NULL,
	Visible bit NOT NULL,
	RoleName nvarchar(128) NOT NULL,
	IsInactive bit NOT NULL,
 CONSTRAINT PK_RoleAccess PRIMARY KEY CLUSTERED 
(
	OID ASC
)
) 
GO
/****** Object:  Default DF_SysRole_IsInactive    Script Date: 12/27/2012 09:10:07 ******/
ALTER TABLE SysRole ADD  CONSTRAINT DF_SysRole_IsInactive  DEFAULT ((0)) FOR IsInactive
GO
/****** Object:  Default DF_SysUser_IsInactive    Script Date: 12/27/2012 09:10:07 ******/
ALTER TABLE SysUser ADD  CONSTRAINT DF_SysUser_IsInactive  DEFAULT ((0)) FOR IsInactive
GO
/****** Object:  ForeignKey FK_RoleAccesse_SysRole    Script Date: 12/27/2012 09:10:07 ******/
ALTER TABLE RoleAccess  WITH CHECK ADD  CONSTRAINT FK_RoleAccesse_SysRole FOREIGN KEY(RoleName)
REFERENCES SysRole (RoleName)
GO
ALTER TABLE RoleAccess CHECK CONSTRAINT FK_RoleAccesse_SysRole
GO
/****** Object:  ForeignKey FK_SysUserRole_SysRole    Script Date: 12/27/2012 09:10:07 ******/
ALTER TABLE SysUserRole  WITH CHECK ADD  CONSTRAINT FK_SysUserRole_SysRole FOREIGN KEY(RoleName)
REFERENCES SysRole (RoleName)
GO
ALTER TABLE SysUserRole CHECK CONSTRAINT FK_SysUserRole_SysRole
GO
/****** Object:  ForeignKey FK_SysUserRole_SysUserRole    Script Date: 12/27/2012 09:10:07 ******/
ALTER TABLE SysUserRole  WITH CHECK ADD  CONSTRAINT FK_SysUserRole_SysUserRole FOREIGN KEY(UserName)
REFERENCES SysUser (UserName)
GO
ALTER TABLE SysUserRole CHECK CONSTRAINT FK_SysUserRole_SysUserRole
GO
